package agendacontactos;


import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Aula_04_03
 */
public class ConectorBD {
    protected Connection conn;
    protected String nombreBD;
    
    
    public ConectorBD(String nombreBD){
        this.nombreBD=nombreBD;
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConectorBD.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        File archivo=new File(this.nombreBD);
        
        if(!archivo.exists()){
            Statement sent=this.conectarBD();
            try {
                sent.executeUpdate("CREATE TABLE tContactos("
                        + "idContacto INTEGER AUTOINCREMENT PRIMARY KEY,"
                        + "nombre VARCHAR(50),"
                        + "apellidos VARCHAR(100),"
                        + "telefono VARCHAR(9))");
                this.conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(ConectorBD.class.getName()).log(Level.SEVERE, null, ex);
            }
            
            
        }
        
    }
    
    protected final Statement conectarBD(){
        Statement sent=null;
        try {
            this.conn=DriverManager.getConnection("jdbc:sqlite:"+this.nombreBD);
            sent=this.conn.createStatement();
            
        } catch (SQLException ex) {
            Logger.getLogger(ConectorBD.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return sent;
    }
    
    protected void añadirContacto(Contacto cont,Statement sent){
        try {
            sent.executeUpdate("INSERT INTO tContactos(nombre,apellidos,telefono) "
                    + "VALUES('"+cont.nombre+"','"+
                    cont.apellidos+"','"+cont.telefono+"')");
            this.conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConectorBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    protected void modificarContacto(Contacto cont,Statement sent){
        try {
            sent.executeUpdate("UPDATE tContactos SET"
                    + "(nombre='"+cont.nombre+"',"
                    + "apellidos='"+cont.apellidos+"',"
                    + "telefono='"+cont.telefono+"')"
                    + " WHERE idContacto="+cont.idContacto);
            this.conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConectorBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void guardarContacto(Contacto cont){
        Statement sent=this.conectarBD();
        ResultSet rs;
        try {
            rs=sent.executeQuery("SELECT idContacto "
                    + "FROM tContactos "
                    + "WHERE idContacto="+cont.idContacto);
        if(rs.next()){
            this.modificarContacto(cont, sent);
        }else{
            this.añadirContacto(cont, sent);
        }
        
        } catch (SQLException ex) {
            Logger.getLogger(ConectorBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    
    }
    
    public ArrayList<Contacto> listadoContactos(char letra){
        ArrayList<Contacto> listaContactos=new ArrayList();
        Statement sent=this.conectarBD();
        ResultSet rs;
        try {
            rs=sent.executeQuery("SELECT * FROM tContactos "
                    + "WHERE apellidos LIKE '"+String.valueOf(letra)+"%'");
            while(rs.next()){
                listaContactos.add(new Contacto(rs.getInt("idContacto"),
                        rs.getString("nombre"),
                        rs.getString("apellidos"),
                        rs.getString("telefono")));
            }
            this.conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConectorBD.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        return listaContactos;
    }
    
    public void eliminarContacto(Contacto c){
        Statement sent=this.conectarBD();
        try {
            sent.executeUpdate("DELETE FROM tContactos WHERE idContacto="+c.idContacto);
       this.conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConectorBD.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
    }
}
